﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;

using ECS.BLL.Entity;
using ECS.DAL.Entity;

namespace ECS.DAL.Component
{
    public class SlipInfoComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = "SELECT dbo.usf_format_mawb(mawb.mawb_prefix, mawb.mawb_sn) displayMawb, shipment_type, hawb, agent_code+' '+agent_name displayAgent, " +
                                        " carrier+flight_number+' '+dbo.usf_format_flight_date(flight_date_std) flightNo, nature_of_goods, " +
                                        " mawb.[piece_count], ews_required, slip_number, partial_seq,RTRIM( LTRIM(UPPER(mawb.[create_by]))) as create_by, partial_acceptance_flag, " +
                                        " sum(gross_weight) sumGrossWeight, sum(tare_weight) sumTareWeight, sum(net_weight) sumNetWeight, " +
                                        " [length], [width], [height], dim.[piece_count] as ItemCount ,dim.dimension_id, mawb.destination, " +
                                        //" CASE WHEN cmtype.comment_name = 'LABEL' THEN comt.value " +
	                                    //" ELSE null END as comment_value, " +

                                        //START test subquery other infomation chen 2015.05.20
                                        " kslip.value as comment_value, " +
                                        //END test subquery other infomation chen 2015.05.20

                                        " dim.dimension_number,slip.scale_id " +
                                        " FROM [dbo].[t_eics_mawb] mawb " +
                                        " INNER JOIN [dbo].[t_eics_wgt_slip] slip ON mawb.[mawb_id] = slip.mawb_id " +
                                        " INNER JOIN [t_eics_weight] wgt ON slip.slip_id = wgt.slip_id " +
                                        " INNER JOIN [dbo].[t_eics_dimension] dim ON slip.slip_id = dim.slip_id " +
                                        //" LEFT JOIN t_eics_comment comt ON slip.slip_id = comt.slip_id " +
                                        //" LEFT JOIN t_eics_comment_type cmtype ON comt.comment_type_id = cmtype.comment_type_id " +
                                        
                                        //START test subquery other infomation chen 2015.05.20
                                        " LEFT JOIN t_eics_comment kslip ON slip.slip_id = kslip.slip_id " +
								        " AND kslip.comment_id in ( " +
		                                " SELECT top 1 comt.comment_id " +
		                                " FROM t_eics_comment comt " +
		                                " INNER JOIN t_eics_comment_type cmtype ON comt.comment_type_id = cmtype.comment_type_id " +
		                                " WHERE cmtype.comment_name = 'LABEL' " +
			                            " AND comt.slip_id = slip.slip_id " +
		                                " order by comt.create_date ) " +
                                        //END test chen 2015.05.20

                                        " WHERE slip_number = '@slip_number' " +
                                        " AND mawb.mawb_prefix = '@mawb_prefix' " +
                                        " AND mawb.mawb_sn = '@mawb_sn' " +
                                        " AND mawb.hawb = '@hawb' " +
                                        //" AND slip.scale_id  = '@scale_id' " +
                                        " GROUP BY mawb.mawb_prefix, mawb.mawb_sn , shipment_type, hawb, agent_code+' '+agent_name , " +
                                        " carrier+flight_number+' '+dbo.usf_format_flight_date(flight_date_std), nature_of_goods, " +
                                        " mawb.[piece_count], ews_required, slip_number, partial_seq, mawb.[create_by], partial_acceptance_flag, " +
                                        " [length], [width], [height], dim.[piece_count],dim.dimension_id, mawb.destination, " +
                                        //" cmtype.comment_name, comt.value, "+

                                        //START test subquery other infomation chen 2015.05.20
                                        " kslip.value, " +
                                        //END test subquery other infomation chen 2015.05.20

                                        " dim.dimension_number,slip.scale_id " +
                                        " ORDER BY dim.dimension_number";


        #endregion

        #region public properties
        
        #endregion

        #region constructor
        public SlipInfoComponent()
        { 
        
        }
        #endregion

        #region private methods
        
        #endregion

        #region public mehods
        /// <summary>
        /// get Pre-manifest data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<DAL.Entity.SlipInfo> GetSlipInfo(ReportCriteria parameter)
        {
            List<DAL.Entity.SlipInfo> items = new List<Entity.SlipInfo>();
            using (DAL.Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_QUERY);

                if (!string.IsNullOrEmpty(parameter.SlipNo))
                {
                    sqlString.Replace("@slip_number", parameter.SlipNo);
                }
                sqlString.Replace("@mawb_prefix", parameter.MawbPrefix);
                sqlString.Replace("@mawb_sn", parameter.MawbSN);
                sqlString.Replace("@hawb", parameter.Hawb);
                if (!string.IsNullOrEmpty(parameter.ScaleId))
                {
                    sqlString.Replace("@scale_id", parameter.ScaleId);
                }
                entities.Open();
                //Console.WriteLine(sqlString.ToString());
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DAL.Entity.SlipInfo data = new Entity.SlipInfo();
                    data.displayMawb = base.GetString(0, reader);
                    data.shipment_type = base.GetString(1, reader);
                    data.hawb = base.GetString(2, reader);
                    data.displayAgent = base.GetString(3, reader);
                    data.flightNo = base.GetString(4, reader);
                    data.nature_of_goods = base.GetString(5, reader);
                    data.piece_count = (int)base.GetInt(6, reader);
                    data.ews_required = base.GetString(7, reader);
                    data.slip_number = base.GetString(8, reader);
                    data.partial_seq = (int)base.GetInt(9, reader);
                    data.create_by = base.GetString(10, reader);
                    data.partial_acceptance_flag = base.GetString(11, reader);
                    data.sumGrossWeight = (double)base.GetDouble(12, reader);
                    data.sumTareWeight = (double)base.GetDouble(13, reader);
                    data.sumNetWeight = (double)base.GetDouble(14, reader);
                    data.length = (double)base.GetDouble(15, reader);
                    data.width = (double)base.GetDouble(16, reader);
                    data.height = (double)base.GetDouble(17, reader);
                    data.ItemCount = (int)base.GetInt(18, reader);
                    data.destination = base.GetString(20, reader);
                    data.comment_value = base.GetString(21, reader);
                    data.scale_id = base.GetString(23, reader);
                    items.Add(data);
                }
            }
            return items;
        }
        /*
        public override void Insert(ObjectContext param)
        {
            //using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            //{
            //    using (Entities entities = new Entities())
            //    {
            //        //insert command
            //        int m_iRecordChanged = entities.SaveChanges();
            //        if (m_iRecordChanged < 1)
            //        {
            //            throw new Exception("Manifest is not inserted.");
            //        }
            //    }
            //    transaction.Complete();
            //}
        }
        public override void Update(ObjectContext param)
        {
            //using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            //{
            //    using (Entities entities = new Entities())
            //    {
            //        //update command
            //        int m_iRecordChanged = entities.SaveChanges();
            //        if (m_iRecordChanged < 1)
            //        {
            //            throw new Exception("Manifest is not updated.");
            //        }
            //    }
            //    transaction.Complete();
            //}
        }
        public override void Delete(ObjectContext param)
        {
            //using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            //{
            //    using (Entities entities = new Entities())
            //    {
            //        //Delete command
            //        int m_iRecordChanged = entities.SaveChanges();
            //        if (m_iRecordChanged < 1)
            //        {
            //            throw new Exception("Manifest is not deleted.");
            //        }
            //    }
            //    transaction.Complete();
            //}
        }
        */        
        #endregion
    }
}
